#!/bin/bash


# 导出 oracle 中的存储过程类型可选
# Set environment variables for UTF-8 encoding
export LANG=en_US.UTF-8
export LC_ALL=en_US.UTF-8
export NLS_LANG=AMERICAN_AMERICA.UTF8

# 设置数据库连接信息
USERNAME="your_username"
PASSWORD="your_password"
HOST="your_host"
PORT="your_port"
SERVICE_NAME="your_service_name"

# 设置输出目录
OUTPUT_DIR="./db_objects"
TARGET_DIR="db_objects/pck"

# 创建输出目录（如果不存在）
mkdir -p "$TARGET_DIR"

# 设置要导出的对象类型（以逗号分隔）
# 可选类型：PROCEDURE, FUNCTION, TRIGGER, PACKAGE, PACKAGE BODY
OBJECT_TYPES="PROCEDURE,FUNCTION,TRIGGER,PACKAGE,PACKAGE BODY"

# 根据配置的对象类型生成 SQL 查询条件
IFS=',' read -ra OBJECT_TYPE_ARRAY <<< "${OBJECT_TYPES}"
OBJECT_TYPE_CONDITION="OBJECT_TYPE = '${OBJECT_TYPE_ARRAY[0]}'"
for i in "${OBJECT_TYPE_ARRAY[@]:1}"; do
  OBJECT_TYPE_CONDITION+=" OR OBJECT_TYPE = '${i}'"
done

# 从数据库中获取所有指定类型的对象名称和类型
OBJECTS=$(sqlplus -s "${USERNAME}/${PASSWORD}@${HOST}:${PORT}/${SERVICE_NAME}" <<EOF
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SELECT OBJECT_NAME || ',' || OBJECT_TYPE FROM USER_OBJECTS WHERE ${OBJECT_TYPE_CONDITION};
EXIT;
EOF
)
# 遍历对象列表
while IFS=',' read -r OBJECT_NAME OBJECT_TYPE; do
  if [ "${OBJECT_TYPE}" != "PACKAGE BODY" ]; then
    # 导出除 PACKAGE BODY 外的对象到文件
    sqlplus -s "${USERNAME}/${PASSWORD}@${HOST}:${PORT}/${SERVICE_NAME}" > "${OUTPUT_DIR}/${OBJECT_NAME}.${OBJECT_TYPE}.sql" <<EOF
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET LINESIZE 1000
SET LONG 20000000
SET LONGCHUNKSIZE 2000
SELECT DBMS_METADATA.GET_DDL('${OBJECT_TYPE}', '${OBJECT_NAME}') FROM DUAL;
EXIT;
EOF
  else
    # 导出 PACKAGE 和 PACKAGE BODY 到文件
    sqlplus -s "${USERNAME}/${PASSWORD}@${HOST}:${PORT}/${SERVICE_NAME}" > "${OUTPUT_DIR}/${OBJECT_NAME}.${OBJECT_TYPE}.sql" <<EOF
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET LINESIZE 1000
SET LONG 20000000
SET LONGCHUNKSIZE 2000
SELECT DBMS_METADATA.GET_DDL('PACKAGE', '${OBJECT_NAME}') FROM DUAL;
EXIT;
EOF

    sqlplus -s "${USERNAME}/${PASSWORD}@${HOST}:${PORT}/${SERVICE_NAME}" >> "${OUTPUT_DIR}/${OBJECT_NAME}.${OBJECT_TYPE}.sql" <<EOF
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET LINESIZE 1000
SET LONG 20000000
SET LONGCHUNKSIZE 2000
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY', '${OBJECT_NAME}') FROM DUAL;
EXIT;
EOF
  fi
  
# SET LONG 20000000 生产使用注意此值
  # 移除尾随空格和多余的换行符
 # sed -i 's/[[:blank:]]*$//' "${OUTPUT_DIR}/${OBJECT_NAME}.${OBJECT_TYPE}.sql"
 # sed -i '/^$/N;/^\n$/D' "${OUTPUT_DIR}/${OBJECT_NAME}.${OBJECT_TYPE}.sql"
 #删除存储过程名称中的用户名和双引号
sed -i 's/".*"\."//g;s/"//g' "${OUTPUT_DIR}/${OBJECT_NAME}.${OBJECT_TYPE}.sql"

done <<< "${OBJECTS}"

# Loop through all .sql files in the source directory
for SQL_FILE in "${SOURCE_DIR}"/*.sql; do
  # Get the file's base name without extension
  FILE_BASENAME=$(basename "${SQL_FILE}" .sql)

  # Copy the .sql file to the target directory and change its extension to .pck
  cp "${SQL_FILE}" "${TARGET_DIR}/${FILE_BASENAME}.pck"
done

echo "Files copied and extensions changed to .pck."

echo "Export completed."